
********************************************************************************
*This file calculates Bank LPI
********************************************************************************

*set working directory
global folder ".."


global HY 0 // HY=1/5 means that proxy for loan liquidity using HY bonds


use "../data_input/data_bank.dta", clear

* Use the ratio between haircut and IRC of corporate bonds to do the extrapolation
egen awgt_bond_mean = mean(awgt_bond)
egen awgt_bond_ORG0_mean = mean(awgt_bond_ORG0)

local list ag_deb ag_mbscmo cash cmoabs com_loan cons_loan fasset intl mm muni mf_realestate sf_realestate o_realestate
foreach x of local list{
	gen awgt_`x'= 1 - (1 - awgt_`x'_ORG0) * (1 - awgt_bond_mean) / (1 - awgt_bond_ORG0_mean)
}
drop awgt_bond_mean awgt_bond_ORG0_mean

* Use the ratio in adi's paper for loans
local list com_loan cons_loan mf_realestate sf_realestate o_realestate
foreach x of local list{
	replace awgt_`x'= 1 - (1 - awgt_bond) * .661/.137
}

*-------------------------------------------------------------------------------------------------*
* Use HY bonds as proxy for loan liquidity
*-------------------------------------------------------------------------------------------------*
if $HY>0 { 
merge m:1 yq using "$folder/data_input/mkt_IRC_HY_vol.dta"
drop if _m==2
drop _m

local list com_loan cons_loan mf_realestate sf_realestate o_realestate
foreach x of local list{
	replace awgt_`x'= 1 - mkt_IRC_HY*$HY
}
}

destring year, replace

* ------------------------------------------------------------------------------
* preserved value in run
*
* ------------------------------------------------------------------------------

gen assetstotal=a_b_ag_deb+ a_b_ag_mbscmo+ a_b_bond+ a_b_cash+ a_b_cmoabs+ a_b_com_loan+ a_b_cons_loan + a_b_eq+ a_b_mm+ a_b_muni+ a_b_treas+a_b_o_realestate+a_b_sf_realestate+a_b_fasset

gen liq_value=a_b_ag_deb*awgt_ag_deb + a_b_ag_mbscmo*awgt_ag_mbscmo+ a_b_bond*awgt_bond+ a_b_cash*awgt_cash+ a_b_cmoabs*awgt_cmoabs+ a_b_com_loan*awgt_com_loan+ a_b_cons_loan*awgt_cons_loan + a_b_eq*awgt_eq+  a_b_muni*awgt_muni+ a_b_treas* awgt_treas+a_b_o_realestate*awgt_o_realestate+a_b_sf_realestate*awgt_sf_realestate+a_b_fasset*awgt_fasset


sort rssd year quarter
bysort rssd: g assetstotal_L = assetstotal[_n-1]

reg assetstotal assetstotal_L
predict assetstotal_hat  
bysort rssd: g liq_value_pct_lag=liq_value/assetstotal_L //using lag weight works add noise (assume portfolio weights are random walks)
bysort rssd: g liq_value_pct=liq_value/assetstotal // using current weight works better (assume perfect forsight)


*winsor2 liq_value_pct, replace cuts(1 99)
replace liq_value_pct=1 if liq_value_pct>1 // the liquidation value>1 because of using lagged asset value
sum liq_value_pct, d

**winsor2 liq_value_pct_lag, replace cuts(1 99)
replace liq_value_pct_lag=1 if liq_value_pct_lag>1 // the liquidation value>1 because of using lagged asset value
sum liq_value_pct_lag, d

* ------------------------------------------------------------------------------
* use bank failure data to refine the outflows
*
* ------------------------------------------------------------------------------

*convert variable names to merge to flow data 
gen tq = yq(year, quarter)
format tq %tq
duplicates drop tq rssd, force
merge 1:1 tq rssd using "$folder/data_input/bank_flow.dta"

replace year=yofd(dofq(tq))
drop if year<2011
drop if year==2018


replace depflow=-100 if faildummy==1
replace totalliabflow=-100 if   faildummy==1


sum liq_value_pct, d

gen lpi = 0 if -totalliabflow>liq_value_pct
replace lpi = 1/liq_value_pct-1 if -totalliabflow<liq_value_pct


g expected_lti = lpi // create the same variable names


gen lpi_lag = 0 if -totalliabflow>liq_value_pct_lag
replace lpi_lag = 1/liq_value_pct_lag-1 if -totalliabflow<liq_value_pct_lag


g expected_lti_lag = lpi_lag // create the same variable names


sum expected_lti, d

drop _merge

gen expected_total_lti=expected_lti*assetstotal 
gen expected_total_lti_lag=expected_lti_lag*assetstotal 

order rssd tq year quarter expected_lti expected_total_lti expected_lti_lag expected_total_lti_lag assetstotal
sum expected_lti, d
if $HY==0 {
save "$folder/data_intermediate/bank_ltisample_haircutrobust_new.dta", replace
}
	
if $HY>0 {
save "$folder/data_intermediate/bank_ltisample_haircutrobust_$HY.dta", replace
}

